--------------------------------------------------------
--  File created - Friday-April-20-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table CONTACTS
--------------------------------------------------------

  CREATE TABLE "SAMPLE_ACCESS"."CONTACTS" 
   (	"CONTACT_NUMBER" NUMBER(11,0), 
	"CONTACT_ID" VARCHAR2(255 CHAR), 
	"CONTACT_NAME" VARCHAR2(255 CHAR), 
	"CONTACT_PHONE" VARCHAR2(255 CHAR), 
	"CONTACT_EMAIL" VARCHAR2(255 CHAR), 
	"CONTACT_CITY" VARCHAR2(255 CHAR), 
	"CONTACT_STATE" VARCHAR2(255 CHAR), 
	"CONTACT_ADDRESS" VARCHAR2(255 CHAR), 
	"CONTACT_ZIP" VARCHAR2(255 CHAR)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into SAMPLE_ACCESS.CONTACTS
SET DEFINE OFF;
Insert into SAMPLE_ACCESS.CONTACTS (CONTACT_NUMBER,CONTACT_ID,CONTACT_NAME,CONTACT_PHONE,CONTACT_EMAIL,CONTACT_CITY,CONTACT_STATE,CONTACT_ADDRESS,CONTACT_ZIP) values (1,'1','Frito','1234567890','no@no.com','San Antonio','TX','123 Blah Rd.','78251');
--------------------------------------------------------
--  DDL for Index PK_CONTACTS
--------------------------------------------------------

  CREATE UNIQUE INDEX "SAMPLE_ACCESS"."PK_CONTACTS" ON "SAMPLE_ACCESS"."CONTACTS" ("CONTACT_NUMBER") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index SRVR_PRTY_ROLE_ID
--------------------------------------------------------

  CREATE INDEX "SAMPLE_ACCESS"."SRVR_PRTY_ROLE_ID" ON "SAMPLE_ACCESS"."CONTACTS" ("CONTACT_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table CONTACTS
--------------------------------------------------------

  ALTER TABLE "SAMPLE_ACCESS"."CONTACTS" ADD CONSTRAINT "PK_CONTACTS" PRIMARY KEY ("CONTACT_NUMBER")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
  ALTER TABLE "SAMPLE_ACCESS"."CONTACTS" MODIFY ("CONTACT_NUMBER" NOT NULL ENABLE);

--------------------------------------------------------
--  DDL for Trigger CONTACTS_CONTACT_NUMBER_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SAMPLE_ACCESS"."CONTACTS_CONTACT_NUMBER_TRG" BEFORE INSERT ON Contacts
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.Contact_Number IS NULL THEN
    SELECT  Contacts_Contact_Number_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT NVL(max(Contact_Number),0) INTO v_newVal FROM Contacts;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT Contacts_Contact_Number_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    -- save this to emulate @@identity
   msaccess_utilities.identity := v_newVal; 
   -- assign the value from the sequence to emulate the identity column
   :new.Contact_Number := v_newVal;
  END IF;
END;
/
ALTER TRIGGER "SAMPLE_ACCESS"."CONTACTS_CONTACT_NUMBER_TRG" DISABLE;


--------------------------------------------------------
--  File created - Friday-April-20-2012   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table RANDOMTABLE
--------------------------------------------------------

  CREATE TABLE "SAMPLE_ACCESS"."RANDOMTABLE" 
   (	"RANDOM_TABLE_ID" NUMBER(11,0), 
	"COLUMN1" VARCHAR2(255 CHAR), 
	"COLUMN2" VARCHAR2(255 CHAR), 
	"YET_ANOTHER_COLUMN" VARCHAR2(255 CHAR), 
	"DATE_COLUMN" DATE, 
	"CHAR_COLUMN" CHAR(20 BYTE), 
	"NUMBER_COLUMN" NUMBER(5,2)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into SAMPLE_ACCESS.RANDOMTABLE
SET DEFINE OFF;
Insert into SAMPLE_ACCESS.RANDOMTABLE (RANDOM_TABLE_ID,COLUMN1,COLUMN2,YET_ANOTHER_COLUMN,DATE_COLUMN,CHAR_COLUMN,NUMBER_COLUMN) values (1,'1','1','1',to_date('12-APR-12','DD-MON-RR'),'1                   ',1);
--------------------------------------------------------
--  DDL for Index PK_RANDOMTABLE
--------------------------------------------------------

  CREATE UNIQUE INDEX "SAMPLE_ACCESS"."PK_RANDOMTABLE" ON "SAMPLE_ACCESS"."RANDOMTABLE" ("RANDOM_TABLE_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table RANDOMTABLE
--------------------------------------------------------

  ALTER TABLE "SAMPLE_ACCESS"."RANDOMTABLE" ADD CONSTRAINT "PK_RANDOMTABLE" PRIMARY KEY ("RANDOM_TABLE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
  ALTER TABLE "SAMPLE_ACCESS"."RANDOMTABLE" MODIFY ("RANDOM_TABLE_ID" NOT NULL ENABLE);

--------------------------------------------------------
--  DDL for Trigger RANDOMTABLE_ID_TRG
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "SAMPLE_ACCESS"."RANDOMTABLE_ID_TRG" BEFORE INSERT ON RandomTable
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.ID IS NULL THEN
    SELECT  RandomTable_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT NVL(max(ID),0) INTO v_newVal FROM RandomTable;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT RandomTable_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    -- save this to emulate @@identity
   msaccess_utilities.identity := v_newVal; 
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
END;
/
ALTER TRIGGER "SAMPLE_ACCESS"."RANDOMTABLE_ID_TRG" DISABLE;
